Explain詳解(優化查詢好幫手)-Part2(possible_keys、key、key_len、ref、rows、filtered、Extra、Json格式的執行計畫)

2021 iThome 鐵人賽

DAY 25

自我挑戰組

那些Mysql我不知道的事系列第 25 篇

Explain詳解(優化查詢好幫手)-Part2(possible_keys、key、key_len、ref、rows、filtered、Extra、Json格式的執行計畫)

13th鐵人賽

百里雪焱

2021-09-26 15:09:46

871 瀏覽

分享至

此篇為前篇的延續唷！

方便閱讀再貼一次基本的explain及測試表

mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.05 sec)

mysql> create table single_table(
    -> id int not null auto_increment,
    -> key1 varchar(10),
    -> key2 int,
    -> key3 varchar(100),
    -> key_part1 varchar(100),
    -> key_part2 varchar(100),
    -> key_part3 varchar(100),
    -> common_field varchar(100),
    -> primary key (id),
    -> key idx_key1 (key1),
    -> unique key uk_key2 (key2),
    -> key idx_key3 (key3),
    -> key idx_key_part(key_part1, key_part2, key_part3)
    -> ) engine=InnoDB charset=utf8;
Query OK, 0 rows affected, 1 warning (0.05 sec)

為id列建立的聚簇索引
為key1列建立的idx_key1二級索引
為key2列建立的uk_key2二級索引，且是唯一
為key3列建立的idx_key3二級索引
為key_part1、key_part2、key_part3列建立的idx_key_part二級索引，這也是個聯合索引

而single_table2跟single_table一樣。

possible_keys與key
possible_keys表示可能會用到的索引，key表示分別使用索引比較成本後最終決定使用的索引。

key_len
我們知道要使用索引來查詢時，要先搞清楚掃描區間及對應的邊界條件，但有些情況下我們希望從執行計畫中就可以看出行成掃描區間的邊界條件是什麼！這時key_len就可以派上用場。
這邊是根據資料儲存的類型(固定長度類型或是可變長度類型)所佔用的長度及是否可以儲存NULL值來計算這個數字，
透過這個數字可以判斷出形成掃描區間的邊界條件是什麼。

ref
跟type有點關係，顯示與索引相等匹配的條件是什麼。

mysql> explain select * from single_table where key1 = 'key11';
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table        | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | single_table | NULL       | ref  | idx_key1      | idx_key1 | 33      | const |    1 |   100.00 | NULL  |
+----+-------------+--------------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

以上面的例子，與索引相等匹配的條件就是常數(const)。

mysql> explain select * from single_table inner join single_table2 on single_table.id = single_table2.id;
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
|  1 | SIMPLE      | single_table2 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | single_table  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ryan_demo_db.single_table2.id |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

以上面的例子，與索引相等匹配的條件就是ryan_demo_db.single_table2.id。

mysql> explain select * from single_table inner join single_table2 on single_table.id = single_table2.id;
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
| id | select_type | table         | partitions | type   | possible_keys | key     | key_len | ref                           | rows | filtered | Extra |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
|  1 | SIMPLE      | single_table2 | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | single_table  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ryan_demo_db.single_table2.id |    1 |   100.00 | NULL  |
+----+-------------+---------------+------------+--------+---------------+---------+---------+-------------------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

以上面的例子，與索引相等匹配的條件就是ryan_demo_db.single_table2.id。

rows
如果是全表資料掃描就是預估的表行數，如果是使用索引查詢就是要掃描的索引紀錄數。

filtered
計算驅動表扇出的策略。看下面的例子會比較明白。

mysql> explain select * from single_table where key1 > '5000' and common_field = 'a';
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key1      | idx_key1 | 33      | NULL | 5073 |    10.00 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

以這個敘述會形成掃描區間(5000,+無限大)，邊界條件是key1 > 5000
並可以看出使用的索引是idx_key1，使用該索引要掃描的紀錄數是5073，而這邊的filtered是10，意思就是最佳化查詢工具『預測』這5073筆紀錄有10%的資料符合common_field = 'a'條件。

extra
額外補充說明的資訊。這邊列出一些可能會看不懂的。

No tables used：敘述沒有from子句會出現
Impossible where：where子句內的條件永遠為false
Using index/Using index condition：前面表示掃描索引的樹就可以得到需要的值並不需要回表操作，而後面則是有用索引但卻沒有生成合適的邊界條件減少掃描數量。
Using temporary：使用臨時表(去重、排序等)

Json格式的執行計畫

這個可以輔助我們看出目前的執行計畫到底好不好，每個欄位的說明我直接寫在下面的內容裡。

mysql> explain format=json select * from single_table s1 inner join single_table2 s2 on s1.key1 = s2.key2  where s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,//整個查詢只有一個select關鍵字，id為1
    "cost_info": {
      "query_cost": "4105.04"//查詢預計成本4105.04
    },
    "nested_loop": [//使用槽狀結構迴圈連接演算法執行
      {
        "table": {
          "table_name": "s2",//驅動表
          "access_type": "ALL",//存取方法
          "possible_keys": [
            "uk_key2"
          ],
          "rows_examined_per_scan": 1,//查一次s2表大約要掃描的紀錄數
          "rows_produced_per_join": 1,//扇出值
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "0.25",
            "eval_cost": "0.40",
            "prefix_cost": "0.65",//單次查詢s2表的成本
            "data_read_per_join": "1K"//讀取的資料大小
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ]
        }
      },
      {
        "table": {
          "table_name": "s1",//被驅動表
          "access_type": "ALL",
          "possible_keys": [
            "idx_key1"
          ],
          "rows_examined_per_scan": 10146,
          "rows_produced_per_join": 101,
          "filtered": "1.00",
          "using_join_buffer": "hash join",//當被驅動表不能有效利用索引，mysql會分配一塊join_buffer給它加快速度
          "cost_info": {
            "read_cost": "3698.55",
            "eval_cost": "40.58",
            "prefix_cost": "4105.04",
            "data_read_per_join": "153K"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
          "attached_condition": "((`ryan_demo_db`.`s1`.`common_field` = 'a') and (cast(`ryan_demo_db`.`s1`.`key1` as double) = cast(`ryan_demo_db`.`s2`.`key2` as double)))"
        }
      }
    ]
  }
}
1 row in set, 3 warnings (0.00 sec)

ERROR: 
No query specified

Extended Explain

可以在exlain後使用show warnings顯示與這個查詢計劃有關的擴充資訊。
Level：通常就是Note
Code：通常就是1003
Message：如code是1003，這邊顯示就是查詢最佳化工具將敘述重新定義後的敘述！

mysql> explain select * from single_table where key1 > '5000' and common_field = 'a';
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | single_table | NULL       | range | idx_key1      | idx_key1 | 33      | NULL | 5073 |    10.00 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `ryan_demo_db`.`single_table`.`id` AS `id`,`ryan_demo_db`.`single_table`.`key1` AS `key1`,`ryan_demo_db`.`single_table`.`key2` AS `key2`,`ryan_demo_db`.`single_table`.`key3` AS `key3`,`ryan_demo_db`.`single_table`.`key_part1` AS `key_part1`,`ryan_demo_db`.`single_table`.`key_part2` AS `key_part2`,`ryan_demo_db`.`single_table`.`key_part3` AS `key_part3`,`ryan_demo_db`.`single_table`.`common_field` AS `common_field` from `ryan_demo_db`.`single_table` where ((`ryan_demo_db`.`single_table`.`key1` > '5000') and (`ryan_demo_db`.`single_table`.`common_field` = 'a'))
1 row in set (0.04 sec)